package com.tuanzi.loan.business.repository.statistics.impl;

import static com.tuanzi.loan.generated.jooq.loan.tables.CommProductChannel.COMM_PRODUCT_CHANNEL;
import static com.tuanzi.loan.generated.jooq.stat.tables.StatCreditCardKeeper.STAT_CREDIT_CARD_KEEPER;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import org.jooq.Condition;
import org.jooq.GroupField;
import org.jooq.SelectField;
import org.jooq.SelectQuery;
import org.jooq.impl.DSL;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import com.tuanzi.loan.business.repository.statistics.CreditCardKeeperStatRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.statistics.creditCardKeeper.CreditCardKeeperStatRequest;
import com.tuanzi.loan.vo.response.statistics.ActivityAverageJoinStatResponse;
import com.tuanzi.loan.vo.response.statistics.creditCardKeeper.CreditCardKeeperStatResponse;
import com.tuanzi.server.loan_base.model.bean.common.CommUserType;
import com.tuanzi.server.loan_base.model.db.CommProductChannel;


/**
 * 信用卡管家统计
 * @author weibifeng 2018/07/20
 */
@Repository
public class CreditCardKeeperStatRepositoryImpl extends JOOQBaseRepositoryPlus implements CreditCardKeeperStatRepository {
    private static final String ALL_FIELD_ID = "'-1000'";
    private static List<CommProductChannel> channelList;
    private static List<CommUserType> userTypeList;
    private static Map<String, String> channelMap;
    
    public synchronized List<CommProductChannel> getChannelList(){
        if (Objects.isNull(channelList)){
        	channelMap = new HashMap<String, String>();
        	SelectQuery sql = dslContext.select(COMM_PRODUCT_CHANNEL.CHANNEL_CODE, COMM_PRODUCT_CHANNEL.CHANNEL_NAME)
                    .from(COMM_PRODUCT_CHANNEL).orderBy(COMM_PRODUCT_CHANNEL.CHANNEL_CODE.asc()).getQuery();
        	channelList = sql.fetch(record -> {
        		CommProductChannel channel = new CommProductChannel();
        		channel.setChannel_code(record.get(0,Integer.class));
        		channel.setChannel_name(record.get(1,String.class));
        		channelMap.put(String.valueOf(channel.getChannel_code()), channel.getChannel_name());
        		return channel;
        	});
        	CommProductChannel all = new CommProductChannel();
        	all.setChannel_code(-1000);
        	all.setChannel_name("全部");
        	channelList.add(all);
        	Collections.reverse(channelList);
        	channelMap.put("-1000", "全部");
        }
        return channelList;
    }
    
    public List<CommUserType> getUserTypeList(){
        if (Objects.isNull(userTypeList)){
        	userTypeList = new ArrayList<CommUserType> ();
        	userTypeList.add(new CommUserType(-1000,"全部"));
        	userTypeList.add(new CommUserType(1,"新用户"));
        	userTypeList.add(new CommUserType(0,"老用户"));
        }
        return userTypeList;
    }

    private SelectQuery listQuery(CreditCardKeeperStatRequest searchRequest){
    	Condition condition = DSL.trueCondition();
        List<SelectField<?>> selectFields = new ArrayList<>();
        List<GroupField> groupFields = new ArrayList<>();
        
        selectFields.add(STAT_CREDIT_CARD_KEEPER.TIME);
        groupFields.add(STAT_CREDIT_CARD_KEEPER.TIME);
        if (Objects.nonNull(searchRequest.getStartDate())) {
            condition = condition.and(STAT_CREDIT_CARD_KEEPER.TIME.ge(new java.sql.Date(searchRequest.getStartDate().getTime())));
        }
        if (Objects.nonNull(searchRequest.getEndDate())) {
        	condition = condition.and(STAT_CREDIT_CARD_KEEPER.TIME.le(new java.sql.Date(searchRequest.getEndDate().getTime())));
        }
       
        // 渠道
        if (Objects.isNull(searchRequest.getChannelList()) || searchRequest.getChannelList().isEmpty()) {
        	// 全部展开
        	selectFields.add(STAT_CREDIT_CARD_KEEPER.CHANNEL);
            groupFields.add(STAT_CREDIT_CARD_KEEPER.CHANNEL);
        } else if(isSelectQueryAll(searchRequest.getChannelList())) { 
        	// 查询全部
        	selectFields.add(DSL.field(ALL_FIELD_ID).as("channel"));
        } else {
            selectFields.add(STAT_CREDIT_CARD_KEEPER.CHANNEL);
            groupFields.add(STAT_CREDIT_CARD_KEEPER.CHANNEL);
            condition = condition.and(STAT_CREDIT_CARD_KEEPER.CHANNEL.in(searchRequest.getChannelList()));
        }
        
        // 用户类型
        if (Objects.isNull(searchRequest.getUserType()) || searchRequest.getUserType().isEmpty()) {
        	// 全部展开
        	selectFields.add(STAT_CREDIT_CARD_KEEPER.IS_NEW_USER);
            groupFields.add(STAT_CREDIT_CARD_KEEPER.IS_NEW_USER);
        } else if(isSelectQueryAll(searchRequest.getUserType())) {
        	// 查询全部
        	selectFields.add(DSL.field(ALL_FIELD_ID).as("is_new_user"));
        } else {
            selectFields.add(STAT_CREDIT_CARD_KEEPER.IS_NEW_USER);
            groupFields.add(STAT_CREDIT_CARD_KEEPER.IS_NEW_USER);
            condition = condition.and(STAT_CREDIT_CARD_KEEPER.IS_NEW_USER.in(searchRequest.getUserType()));
        }

        return dslContext.select(selectFields)
        		.select(DSL.sum(STAT_CREDIT_CARD_KEEPER.ACTIVITY_UV).as("activity_uv"))
        		.select(DSL.sum(STAT_CREDIT_CARD_KEEPER.ADD_CARD_USER_UV).as("add_card_user_uv"))
                .select(DSL.sum(STAT_CREDIT_CARD_KEEPER.ADD_CARD_UV).as("add_card_uv"))
                .select(DSL.sum(STAT_CREDIT_CARD_KEEPER.NET_BANK_IMPORT_UV).as("net_bank_import_uv"))
                .select(DSL.sum(STAT_CREDIT_CARD_KEEPER.MAIL_IMPORT_UV).as("mail_import_uv"))
                .select(DSL.sum(STAT_CREDIT_CARD_KEEPER.MANUAL_IMPORT_UV).as("manual_import_uv"))
                .select(DSL.sum(STAT_CREDIT_CARD_KEEPER.UPDATE_BILL_UV).as("update_bill_uv"))
                .from(STAT_CREDIT_CARD_KEEPER)
                .where(condition)
                .groupBy(groupFields)
                .orderBy(STAT_CREDIT_CARD_KEEPER.TIME.desc())
                .getQuery();
    }
    
    /**
     * @param searchRequest 请求信息
     * @param pageable      分页信息
     * @return 响应列表
     */
    @Override
    @SuppressWarnings("unchecked")
    public Page<CreditCardKeeperStatResponse> getlistPage(CreditCardKeeperStatRequest searchRequest,Pageable pageable) {
    	SelectQuery selectQuery = listQuery(searchRequest);
    	return search(selectQuery, pageable, record ->{
    		CreditCardKeeperStatResponse response = new CreditCardKeeperStatResponse();
			response.setTime(record.get(0,Date.class));
			
			String channel = record.get(1,String.class);
			response.setChannel(channelMap.get(channel));
			
			String userType = record.get(2,String.class);
			switch(userType) {
			case "0": {
				response.setIs_new_user("老用户");
				break;
			}
			case "1": {
				response.setIs_new_user("新用户");
				break;
			}
			case "-1000": {
				response.setIs_new_user("全部");
				break;
			}
			default : {
				response.setIs_new_user("未知");
				break;
			}
			}
			
			response.setActivity_uv(record.get(3,Integer.class));
			response.setAdd_card_user_uv(record.get(4,Integer.class));
			response.setAdd_card_uv(record.get(5,Integer.class));
			response.setNet_bank_import_uv(record.get(6,Integer.class));
			response.setMail_import_uv(record.get(7,Integer.class));
			response.setManual_import_uv(record.get(8,Integer.class));
			response.setUpdate_bill_uv(record.get(9,Integer.class));
			return response;
		});
    }
    
    /**
     * @param searchRequest 请求信息
     * @param pageable      分页信息
     * @return 响应列表
     */
    @Override
    @SuppressWarnings("unchecked")
    public Page<CreditCardKeeperStatResponse> getAllList(CreditCardKeeperStatRequest searchRequest) {
    	SelectQuery selectQuery = listQuery(searchRequest);
    	List<CreditCardKeeperStatResponse> resultList = selectQuery.fetch(record -> {
    		CreditCardKeeperStatResponse response = new CreditCardKeeperStatResponse();
			response.setTime(record.get(0,Date.class));
			
			String channel = record.get(1,String.class);
			response.setChannel(channelMap.get(channel));
			
			String userType = record.get(2,String.class);
			switch(userType) {
			case "0": {
				response.setIs_new_user("老用户");
				break;
			}
			case "1": {
				response.setIs_new_user("新用户");
				break;
			}
			case "-1000": {
				response.setIs_new_user("全部");
				break;
			}
			default : {
				response.setIs_new_user("未知");
				break;
			}
			}
			
			response.setActivity_uv(record.get(3,Integer.class));
			response.setAdd_card_user_uv(record.get(4,Integer.class));
			response.setAdd_card_uv(record.get(5,Integer.class));
			response.setNet_bank_import_uv(record.get(6,Integer.class));
			response.setMail_import_uv(record.get(7,Integer.class));
			response.setManual_import_uv(record.get(8,Integer.class));
			response.setUpdate_bill_uv(record.get(9,Integer.class));
			return response;
    	});
    	return new PageImpl<CreditCardKeeperStatResponse>(resultList);
    }
    
    /******检查是否查询全部*********/
    private boolean isSelectQueryAll(List selectList) {
    	if(selectList == null || selectList.size() == 0) {
    		return false;
    	}
    	for(int i=0; i<selectList.size(); i++) {
    		String s = String.valueOf(selectList.get(i));
    		if("-1000".equals(s)) {
    			return true;
    		}
    	}
    	return false;
    }

    @Override
    protected TableImpl getTable() {
        return STAT_CREDIT_CARD_KEEPER;
    }
}
